﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EntitiesGenerator.Dtos
{
    public class Table
    {
        public Table(string conn, string tableName)
        {
            this.TableName = tableName;
            this.Init(conn, tableName);
        }

        public string TableName { get; set; }

        /// <summary>
        /// 数据表备注
        /// </summary>
        public string Remark { get; set; }

        public List<TableColumn> Columns { get; set; }

        /// <summary>
        /// 初始化表和数据列信息
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="tableName"></param>
        private void Init(string connStr, string tableName)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                var cmd = conn.CreateCommand();

                //获取列信息sql
                string tablecolumnInfosql = $"select top 1 * from {tableName};";

                //获取外键信息sql
                string tableForeignKeyColumnInfosql = $@"
                                            SELECT
	                                            BaseColumnName  = (SELECT name FROM syscolumns WHERE colid = b.fkey AND id = b.fkeyid) ,
	                                            ReferencesTableName = object_name(b.rkeyid) ,
	                                            ReferencesColumnName = (SELECT name FROM syscolumns WHERE colid = b.rkey AND id = b.rkeyid)
                                            FROM sysobjects a 
                                                JOIN sysforeignkeys b ON a.id = b.constid 
                                                JOIN sysobjects c ON a.parent_obj = c.id 
                                            where object_name(b.fkeyid) = '{TableName}';";

                //获取主键信息sql
                string tablePrimaryKeyInfoSql = $@"
                                            SELECT
                                                syscolumns.name as ColumnName
                                            FROM
                                                syscolumns,
                                                sysobjects,
                                                sysindexes,
                                                sysindexkeys
                                            WHERE
                                                syscolumns.id = object_id('{TableName}') AND
                                                sysobjects.xtype = 'PK' AND
                                                sysobjects.parent_obj = syscolumns.id AND
                                                sysindexes.id = syscolumns.id AND
                                                sysobjects.name = sysindexes.name AND
                                                sysindexkeys.id = syscolumns.id AND
                                                sysindexkeys.indid = sysindexes.indid AND
                                                syscolumns.colid = sysindexkeys.colid; ";

                //获取列备注信息
                string tableColumnRemartInfoSql = $@"
                                            SELECT
                                                col.name as ColumnName,
                                                ISNULL(ep.[value], '') Remark

                                            FROM    dbo.syscolumns col
                                                INNER JOIN dbo.sysobjects obj ON col.id = obj.id
                                                                                    AND obj.xtype IN( 'U', 'v' )
                                                                                    AND obj.status >= 0
                                                LEFT JOIN sys.extended_properties ep ON col.id = ep.major_id
                                                                                            AND col.colid = ep.minor_id
                                                                                            AND ep.name = 'MS_Description'


                                            WHERE

                                                obj.name IN('{TableName}');";

                //获取表备注信息
                string tableRemarkSql = $@"
                                        select                                         
                                          isnull(g.[value], '-') AS Remark
                                        from
                                          sys.tables a left
                                        join sys.extended_properties g

                                        on (a.object_id = g.major_id AND g.minor_id = 0)
                                        where a.name = '{TableName}'; ";



                cmd.CommandText = tablecolumnInfosql;
                var reader = cmd.ExecuteReader();
                var tablecolumnInfoTable = reader.GetSchemaTable();
                reader.Close();

                DataTable tableForeignKeyColumnInfoTable = new DataTable();
                DataTable tablePrimaryKeyInfoTable = new DataTable();
                DataTable tableColumnRemartInfoTable = new DataTable();

                SqlDataAdapter adapter = new SqlDataAdapter(cmd);

                cmd.CommandText = tableForeignKeyColumnInfosql;
                adapter.Fill(tableForeignKeyColumnInfoTable);

                cmd.CommandText = tablePrimaryKeyInfoSql;
                adapter.Fill(tablePrimaryKeyInfoTable);


                cmd.CommandText = tableColumnRemartInfoSql;
                adapter.Fill(tableColumnRemartInfoTable);


                cmd.CommandText = tableRemarkSql;
                //表备注信息
                this.Remark = cmd.ExecuteScalar().ToString();
                //外键信息转换为字典
                var foreignKeyInfoDic = tableForeignKeyColumnInfoTable
                    .AsEnumerable()
                    .ToDictionary(
                        r => r["BaseColumnName"].ToString(),
                        r => new ForeignColumn
                        {
                            BaseColumnName = r["BaseColumnName"].ToString(),
                            BaseTableName = TableName,
                            ReferencesTableName = r["ReferencesTableName"].ToString(),
                            ReferencesColumnName = r["ReferencesColumnName"].ToString(),
                        });
                //列备注信息转换为字段
                var columnRemarkInfoDic = tableColumnRemartInfoTable.AsEnumerable().ToDictionary(r => r["ColumnName"], r => r);
                //所有的主键列名
                var primaryKeys = tablePrimaryKeyInfoTable.AsEnumerable().Select(r => r["ColumnName"].ToString()).ToList();

                List<TableColumn> columns = new List<TableColumn>();
                foreach (DataRow row in tablecolumnInfoTable.Rows)
                {
                    TableColumn col = new TableColumn();
                    col.TableName = TableName;
                    col.ColumnName = row["ColumnName"].ToString();
                    col.DataType = Type.GetType(row["DataType"].ToString(), true);
                    col.IsIdentity = row["IsIdentity"].ConvertTo<bool>();
                    col.Nullable = row["AllowDBNull"].ConvertTo<bool>();
                    col.Length = row["ColumnSize"].ConvertTo<long>();
                    col.Remark = columnRemarkInfoDic[col.ColumnName]["Remark"].ToString();
                    col.IsPrimaryKey = primaryKeys.Any(r => r == col.ColumnName);
                    col.ForeignKey = foreignKeyInfoDic.ContainsKey(col.ColumnName) ? foreignKeyInfoDic[col.ColumnName] : null;
                    columns.Add(col);
                }

                this.Columns = columns;
            }
        }

        /// <summary>
        /// 生成实体类
        /// </summary>
        /// <returns></returns>
        public string GeneralEntityClass(string nameSpace, string baseType)
        {
            nameSpace = nameSpace.IsNullOrWhiteSpace() ? "Entities" : nameSpace;

            StringBuilder sb = new StringBuilder();
            int primaryKeyCount = Columns.Count(r => r.IsPrimaryKey);
            int primaryKeyOrder = -1;
            foreach (TableColumn col in Columns)
            {
                if (col.IsPrimaryKey)
                    primaryKeyOrder += 1;

                var property = col.GeneralPropertyString(primaryKeyCount > 1 ? (int?)primaryKeyOrder : null);
                sb.Append(property);
            }

            string classContent = EntityTemplateConst
                                    .EntityTemplate
                                    .Replace("{NameSpace}", nameSpace)
                                    .Replace("{TableName}", TableName)
                                    .Replace("{TableRemark}", Remark)
                                    .Replace("{BaseType}", baseType.IsNullOrWhiteSpace() ? "" : " : " + baseType)
                                    .Replace("{Properties}", sb.ToString());

            return classContent;
        }
    }
}
